﻿create PROCEDURE Sync.SP_LoanExecutionInstallments_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @LoanExecutionInstallments_Temp TABLE 
		(
			LoanExecutionInstallmentID UniqueIdentifier,
			[LoanExecutionID] uniqueidentifier,
				[InstallmentDate] datetime,
				[OriginalInstallmentAmount] float,
				[InterestInstallmentAmount] float,
				[TotalInstallmentAmount] float,
				[PenaltyDays] int,
				[PenaltyAmount] float,
				[IsPaid] bit,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @LoanExecutionInstallments_Temp 
	   SELECT	Tbl.Col.value('LoanExecutionInstallmentID[1]','uniqueidentifier') LoanExecutionInstallmentID,
                Tbl.Col.value('LoanExecutionID[1]', 'uniqueidentifier') [LoanExecutionID],
Tbl.Col.value('InstallmentDate[1]', 'datetime') [InstallmentDate],
Tbl.Col.value('OriginalInstallmentAmount[1]', 'float') [OriginalInstallmentAmount],
Tbl.Col.value('InterestInstallmentAmount[1]', 'float') [InterestInstallmentAmount],
Tbl.Col.value('TotalInstallmentAmount[1]', 'float') [TotalInstallmentAmount],
Tbl.Col.value('PenaltyDays[1]', 'int') [PenaltyDays],
Tbl.Col.value('PenaltyAmount[1]', 'float') [PenaltyAmount],
Tbl.Col.value('IsPaid[1]', 'bit') [IsPaid],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/LoanExecutionInstallments') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[LoanExecutionInstallments] ([LoanExecutionInstallmentID],[LoanExecutionID],[InstallmentDate],[OriginalInstallmentAmount],[InterestInstallmentAmount],[TotalInstallmentAmount],[PenaltyDays],[PenaltyAmount],[IsPaid])
	SELECT t.[LoanExecutionInstallmentID],t.[LoanExecutionID],t.[InstallmentDate],t.[OriginalInstallmentAmount],t.[InterestInstallmentAmount],t.[TotalInstallmentAmount],t.[PenaltyDays],t.[PenaltyAmount],t.[IsPaid]
	FROM  @LoanExecutionInstallments_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.LoanExecutionInstallments_Tracking s
        WHERE t.LoanExecutionInstallmentID = s.LoanExecutionInstallmentID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[LoanExecutionInstallments]
	SET [LoanExecutionInstallments].[LoanExecutionID] = t.[LoanExecutionID],[LoanExecutionInstallments].[InstallmentDate] = t.[InstallmentDate],[LoanExecutionInstallments].[OriginalInstallmentAmount] = t.[OriginalInstallmentAmount],[LoanExecutionInstallments].[InterestInstallmentAmount] = t.[InterestInstallmentAmount],[LoanExecutionInstallments].[TotalInstallmentAmount] = t.[TotalInstallmentAmount],[LoanExecutionInstallments].[PenaltyDays] = t.[PenaltyDays],[LoanExecutionInstallments].[PenaltyAmount] = t.[PenaltyAmount],[LoanExecutionInstallments].[IsPaid] = t.[IsPaid] 
	FROM [dbo].[LoanExecutionInstallments] s JOIN @LoanExecutionInstallments_Temp t 
    ON t.LoanExecutionInstallmentID = s.LoanExecutionInstallmentID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.LoanExecutionInstallments_Tracking r
				  WHERE t.LoanExecutionInstallmentID = r.LoanExecutionInstallmentID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[LoanExecutionInstallments]
    FROM @LoanExecutionInstallments_Temp t JOIN [dbo].[LoanExecutionInstallments] s
    ON t.LoanExecutionInstallmentID = s.LoanExecutionInstallmentID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END











